A materialized view (MV) is an access structure used for performance tuning of SQL queries. A materialized view can be thought of as a special kind of view, which physically exists inside a database. The MV can contain joins and/or aggregates and exists to improve query execution time by pre-calculating expensive joins and aggregation operations prior to execution. A Materialized view is also known as a summary or an aggregate table.
Materialized views eliminate the overhead associated with gathering and deriving the data every time a query is executed. Through a process known as query rewrite, a query can be optimized to recognize and use existing materialized views that could answer the query. Typically, the query rewrite optimization is transparent to an application that submits the query. That is, the rewrite operation happens automatically and does not require the application to know about the existence of materialized views, nor that a particular materialized view has been substituted for the original query.
During query rewrite, the query is analyzed against a specific materialized view, in order to assign instance numbers to duplicate table instances. This is done by comparing the join graphs of the MV and query. Once the instance numbers are assigned, then the remainder of query rewrite algorithms will treat two tables with different instance numbers as if they were different tables.
However, during an initial input of a workload that contains one or more queries, materialized views have not yet been generated and the system must generate appropriate materialized views. Thus, a materialized view does not yet exist that can be used to assign instance numbers in the query.
Assigning instance numbers randomly could cause the creation of unnecessary materialized views. For example, two equivalent queries could have different SQL statements due to different ordering of tables in a FROM clause, different joins in a WHERE clause, or other syntactical differences. If the instance numbers for two equivalent queries are assigned differently, each query would get a different materialized view recommendation even though they could have shared the same materialized view.
A number of commercial tools are available for tuning queries including Oracle's SQL Access Advisor. In the past, queries containing multiple instances of the same table were only considered for MV recommendations that exactly matched the text of the query. This has a disadvantage that if the query is complex, the materialized view will not be incrementally refreshable and further multiple queries will not be able to benefit from the same materialized view.